const util = require('../../common/util');
const config = require('../../common/config');
const sqlite3 = require('sqlite3');

//实现方法：通过评论id找评论，通过用户id找，点赞，踩，写评论，删除评论，置顶
class ReviewDB {
    /**
     * 构造函数
     * @param {String} dbFile 数据库文件路径
     * @constructor
     */
    constructor() {
        this.dbFile = config.dbFile;
        this.instance = null;
        this.db = null;
    }
    // 获取数据库实例
    /**
     * @returns {ReviewDB} ReviewDB实例
     */
    static getInstance() {
        if (!this.instance) {
            try {
                this.instance = new ReviewDB();
            } catch (err) {
                util.err(err);
            }
        }
        util.log("GoodsDB实例已创建");
        return this.instance;
    }

    /**
     * @returns {Promise} Promise对象
     */
    async connect() {
        if (this.isConnected) return; // 避免重复连接
        return new Promise((resolve, reject) => {
            this.db = new sqlite3.Database(this.dbFile, (err) => {
                if (err) {
                    util.err('数据库连接失败', err);
                    return reject(err);
                } else {
                    this.isConnected = true;
                    util.log('数据库连接成功');
                    resolve();
                }
            });
            this.db.on('error', (err) => {
                util.err('数据库连接断开', err);
                this.isConnected = false;
                // 可选：5秒后自动重连
                setTimeout(() => this.connect(), 5000);
            });
        });
    }

    //通过评论id找
    findById(review_id) {
        return new Promise((resolve, reject) => {
            const sql = "SELECT * FROM reviews WHERE review_id = ?";
            this.db.get(sql, [review_id], (err, result) => {
                err ? reject(err) : resolve(result);
            });
        });
    }

    //通过用户id找
    findByUser(user_id) {
        return new Promise((resolve, reject) => {
            const sql = "SELECT * FROM reviews WHERE user_id = ?";
            this.db.get(sql, [user_id], (err, result) => {
                err ? reject(err) : resolve(result);
            });
        });
    }

    //写评论
    add(review) {
        return new Promise((resolve, reject) => {
            const sql = `
                INSERT INTO reviews (
                    user_id,goods_id,
                    content,created_time
                ) VALUES (?, ?, ?, ?);`;
            const values = [
                review.user_id, review.goods_id,
                review.content, new Date().toISOString()
            ];
            this.db.run(sql, values, function (err) {
                if (err) {
                    reject(err);
                } else {
                    util.log(`Inserted reviews with id: ${this.lastID}`);
                    resolve(this.lastID); // 返回插入的goods_id
                }
            });
        });
    }

    useful(review_id,user_id) {
        const sql = `UPDATE reviews SET useful = useful + 1 WHERE review_id = ?`;
        return new Promise((resolve, reject) => {
            this.db.run(sql, [review_id], function (err) {
                err ? reject(err) : resolve();
            })
        })
    }

    useless(review_id) {
        const sql = `UPDATE reviews SET useless = useless + 1 WHERE review_id = ?`;
        return new Promise((resolve, reject) => {
            this.db.run(sql, [review_id], function (err, result) {
                err ? reject(err) : resolve(result);
            })
        })
    }

    setPriority(review_id, priority) {
        const sql = `UPDATE reviews SET priority = ? WHERE review_id = ?`;
        return new Promise((resolve, reject) => {
            this.db.run(sql, [priority, review_id], function (err, result) {
                err ? reject(err) : resolve(result);
            })
        })
    }

    remove(review_id) {
        const sql = `DELETE FROM reviews WHERE review_id = ?`;
        return new Promise((resolve, reject) => {
            this.db.run(sql, [review_id], function (err, result) {
                err ? reject(err) : resolve(result);
                util.log(result);
            })
        })
    }
}

module.exports = ReviewDB;